"""Connect to SQLite Database.  

.. helpdoc::

Sort of a misnomer but I can't think of a better name.  Basically what we want to do is to allow the user to set an arbitrary database on disk and set it into the Red-R schema.  This will simply connect to a database and allow the user to select one of the tables in that database and send it into the Red-R schema as a SQLiteTable
"""

"""<widgetXML>
    <name>
        Connect to SQLite Database
    </name>
    <icon>
        readfile.png
    </icon>
    <summary>
        Connects to an sqlite database.
    </summary>
    <tags>
        <tag priority="0">
            SQLite
        </tag>
    </tags>
    <author>
        <authorname>Kyle R. Covington</authorname>
        <authorcontact>kyle@red-r.org</authorcontact>
    </author>
    </widgetXML>
"""

from OWRpy import *
import redRGUI 
import redREnviron
import re, os, redR
import sqlite3
class connectSQLiteDatabase(OWRpy):
    def __init__(self, **kwargs):

        OWRpy.__init__(self, **kwargs)

        self.recentFiles=['Select File']
        self.database = 'local|temp.db'
        #self.path = redREnviron.settings['workingDir']
        
        
        self.outputs.addOutput("SQLite Data Table", "SQLite Data Table", signals.RedRSQLite.SQLiteDatabase)
        
        #### GUI ####
        area = redRGUI.base.widgetBox(self.controlArea,orientation='horizontal')
        options = redRGUI.base.widgetBox(area,orientation='vertical')
        box = redRGUI.base.groupBox(options, label="Load File", addSpace = True, orientation='horizontal')
        ### set file area
        button = redRGUI.base.button(box, label = 'Browse', callback = self.browseFile)
        redRGUI.base.button(box, label = 'Set Local', callback = self.setLocalDB)
        self.dbinfo = redRGUI.base.widgetLabel(box, self.database)  # put the name of the database here
        ### set table area
        
        self.tableNames = redRGUI.base.listBox(self.controlArea, label = 'Available Tables:', toolTip = 'A list of available tables that can be selected.  The selected table will be sent into the schema.')
        
        self.scanNewFile()
        
        
    def browseFile(self): 
        fn = QFileDialog.getOpenFileName(self, "Open File", redREnviron.settings['workingDir'],
        "Database (*.db *.sqlite);; All Files (*.*)")
        if fn.isEmpty(): return
        self.dbinfo.setText(unicode(fn))
        self.database = unicode(fn)
        redREnviron.settings['workingDir'] = os.path.split(self.database)[0]
        self.scanNewFile()  # scans the database and picks out the table names for sending to the send function.
        
        newData = signals.RedRSQLite.SQLiteDatabase(self, data = self.database)
        self.rSend("SQLite Data Table", newData)
        
    def setLocalDB(self):
        self.dbinfo.setText('local|temp.db')
        self.database = 'local|temp.db'
        self.scanNewFile()
        
        newData = signals.RedRSQLite.SQLiteDatabase(self, data = self.database)
        self.rSend("SQLite Data Table", newData)
        
    def scanNewFile(self):
        ## establish connection and cursor
        if 'local|' in self.database:  # convert the database if the local name is present.      
            import redREnviron
            database = os.path.join(redREnviron.directoryNames['tempDir'], self.database.split('|')[1])
        else:
            database = self.database
        conn = sqlite3.connect(os.path.abspath(database))
        cursor = conn.cursor()
        
        ## query the database for the tables.
        
        cursor.execute('SELECT * FROM (SELECT * FROM SQLITE_MASTER UNION ALL SELECT * FROM SQLITE_TEMP_MASTER) WHERE type="table" OR type ="view"')
        info = []
        for row in cursor:  # collect the info for all of the tables and the views.
            info.append(unicode(row[1])+', '+ unicode(row[0]))
        
        conn.close() # close the connection, no need to commit anything
        
        self.tableNames.update(info)  # place the table names into the tableNames listBox
        
    
        